package com.example.mapp.buildSql;

import com.example.ext.entity.ExtElevator;
import org.apache.commons.lang3.StringUtils;
import org.springframework.util.CollectionUtils;

import java.util.List;
import java.util.Map;

/**
 * Created by simon on 2017/10/12.
 */
public class ElevatorMappSql {

    /**
     * 新增电梯是否参与保险的查询条件
     * @param extElevator
     * @return
     */
    public String queryList(ExtElevator extElevator){

        String select = "select a.*,b.party_name as maintenancePartyName from elevator a " +
                " left join party b on a.maintenance_party_id=b.id " +
                " left join sys_user e on a.safe_user_id = e.id "+
                "where a.used_party_id=#{usedPartyId}";
        if(extElevator.getElevatorNum()!=null && !extElevator.getElevatorNum().equals("")){
            select+=" and (elevator_num=#{elevatorNum} or outside_num=#{elevatorNum})";
        }
        if(extElevator.getUsedPartyId() != null){
            select+=" and a.used_party_id=#{usedPartyId}";
        }
        if(extElevator.getMaintenancePartyId()!=null){
            select+=" and a.maintenance_party_id=#{maintenancePartyId}";
        }
        if(extElevator.getElevatorTypeId()!=null){
            select+=" and a.elevator_type_id=#{elevatorTypeId}";
        }

        return select;
    }

    public String queryMaintList(Map<String, Object> extElevator){

        String select = "select a.*,b.party_name as usedPartyName from elevator a  " +
                " left join party b on a.used_party_id=b.id  " +
                "where a.maintenance_party_id=#{partyId}";
        if(extElevator.get("elevatorNum")!=null && !extElevator.get("elevatorNum").equals("")){
            select+=" and (a.elevator_num=#{elevatorNum} or a.outside_num=#{elevatorNum})";
        }
        if(extElevator.get("usedPartyId")!=null && !extElevator.get("usedPartyId").equals("") ){
            select+=" and a.used_party_id=#{usedPartyId}";
        }
        if(extElevator.get("maintenancePartyId")!=null && !extElevator.get("maintenancePartyId").equals("") ){
            select+=" and a.maintenance_party_id=#{maintenancePartyId}";
        }
        if(extElevator.get("elevatorTypeId")!=null && !extElevator.get("elevatorTypeId").equals("") ){
            select+=" and a.elevator_type_id=#{elevatorTypeId}";
        }
        if(extElevator.get("maintenanceUserFirst")!=null && !extElevator.get("maintenanceUserFirst").equals("") ){
            select+=" and (a.maintenance_user_first=#{maintenanceUserFirst} or a.maintenance_user_second=#{maintenanceUserFirst})";
        }
        return select;
    }

    public String queryJgList(Map<String, Object> extElevator){

        String select = "select a.*,c.party_name as maintenancePartyName,b.party_name as usedPartyName" +
                " from elevator a" +
                " left join party b on a.used_party_id=b.id  " +
                " left join party c on a.maintenance_party_id=c.id " +
                " where 1=1 ";
        if(extElevator.get("partyId")!=null && !extElevator.get("partyId").equals("") ){
            select += " and a.used_party_id in (select id from party where parent_id=#{partyId})";
        }
        if(extElevator.get("elevatorNum")!=null && !extElevator.get("elevatorNum").equals("") ){
            select+=" and (a.elevator_num=#{elevatorNum}  or a.outside_num=#{elevatorNum})";
        }
        if(extElevator.get("usedPartyId")!=null && !extElevator.get("usedPartyId").equals("") ){
            select+=" and a.used_party_id=#{usedPartyId}";
        }
        if(extElevator.get("maintenancePartyId")!=null && !extElevator.get("maintenancePartyId").equals("") ){
            select+=" and a.maintenance_party_id=#{maintenancePartyId}";
        }
        if(extElevator.get("elevatorTypeId")!=null && !extElevator.get("elevatorTypeId").equals("") ){
            select+=" and a.elevator_type_id=#{elevatorTypeId}";
        }
        //添加监管部门查询电梯时，根据电梯所在分局筛选
        if(extElevator.get("branchOffices") != null && !"".equals(extElevator.get("branchOffices"))){

        }
        List<String> branchOffices = (List<String>) extElevator.get("branchOffices");
        if(branchOffices != null && branchOffices.size() > 0){
            StringBuilder str = new StringBuilder();
            for (int i = 0; i < branchOffices.size(); i++) {
                if ("".equals(str.toString())) {
                    str.append("(#{branchOffices["+i+"]}");
                } else {
                    str.append(", #{branchOffices["+i+"]}");
                }
            }
            str.append(")");
            select += " and a.branch_office in "+str.toString();
        }
        select += " order by elevator_num ";
        return select;
    }

    public String getLatLng(Map<String, Object> extElevator,String mark) {

		String select = "SELECT A.ELEVATOR_NUM as elevatorNum,A.LNG as lng,A.LAT as lat,A.ELEVATOR_PLACE as elevatorPlace,A.PLACE_TYPE as placeType,"
				+ "B.TYPE_NAME as typeName FROM ELEVATOR A "
				+ "LEFT JOIN ELEVATOR_TYPE B ON A.ELEVATOR_TYPE_ID = B.ID";
//        if(extElevator.get("partyId")!=null && extElevator.get("partyId")!="" ){
//            select += " and a.used_party_id in (select id from party where parent_id=#{partyId})";
//        }
        String partyId = Integer.toString((Integer) extElevator.get("partyId"));
        //如果是市监管部门
        if ("0".equals(mark)) {
            select += " WHERE A.used_party_id IN " +
                    " (SELECT id FROM party WHERE parent_id IN (" +
                    "SELECT id FROM party WHERE parent_id = "
                    + partyId + " And party_type = '4') And party_type = '2')";
        }
        //如果是区监管部门
        if ("1".equals(mark)) {
            select += " WHERE A.used_party_id IN " +
                    " (SELECT id FROM party WHERE parent_id = " + partyId + " And party_type = '2')";
        }
        //如果是分局监管部门
        if ("2".equals(mark)) {
            select += " WHERE A.used_party_id IN " +
                    " (SELECT id FROM party WHERE parent_id = "+
                    " (SELECT parent_id FROM party where id = " + partyId +")"+
                    " And party_type = '2')";
        }
        return select;
    }


    public String getLatLng_bak(Map<String, Object> extElevator) {

		String select = "SELECT A.ELEVATOR_NUM as elevatorNum,A.LNG as lng,A.LAT as lat,CASE WHEN A.NEXT_CHECK_DATE < DATE_FORMAT(NOW(),'%Y%M%D') THEN '0' ELSE '1' END  checkStatus,"
				+ "CASE WHEN A.NEXT_HALFMON_MAINT_DATE < DATE_FORMAT(NOW(),'%Y%M%D') "
				+ "OR A.NEXT_QUARTERLY_MAINTE_DATE < DATE_FORMAT(NOW(),'%Y%M%D') "
				+ "OR A.NEXT_HALFYEAR_MAINT_DATE < DATE_FORMAT(NOW(),'%Y%M%D') "
				+ "OR A.NEXT_YEAR_MAINT_DATE < DATE_FORMAT(NOW(),'%Y%M%D') THEN '0' ELSE '1' END maintStatus FROM ELEVATOR A ";
//        if(extElevator.get("partyId")!=null && extElevator.get("partyId")!="" ){
//            select += " and a.used_party_id in (select id from party where parent_id=#{partyId})";
//        }
        return select;
    }

    public String updateSafeUser(Map<String,Object> inParam) {
        String update = "update elevator set safe_user_id = #{safeUserId} where 1=1";
        List<Integer> elevatorNums = (List<Integer>) inParam.get("elevatorNums");
        if (!"0".equals(inParam.get("partyType")) && elevatorNums != null && elevatorNums.size() > 0) {
            StringBuilder str = new StringBuilder();
            for (int i = 0; i < elevatorNums.size(); i++) {
                if ("".equals(str.toString())) {
                    str.append("(#{elevatorNums[" + i + "]}");
                } else {
                    str.append(", #{elevatorNums[" + i + "]}");
                }
            }
            str.append(")");
            update += " and elevator_num in " + str.toString();
        }
        return update;
    }

    /**
     * 按需维保，获取可选电梯
     * @param extElevator
     * @return
     */
    public String queryElevatorList(ExtElevator extElevator){
        StringBuilder builder = new StringBuilder();
        builder.append("select * from(\n" +
                "select a.id, a.used_party_id,a.maintenance_party_id, a.outside_num as outsidenum, a.create_company_name," +
                "       a.build_addr as buildAddr,a.elevator_type_id as elevatorTypeId, " +
                "       a.elevator_num as elevatorNum, e.type_name as elevatorTypeName,\n" +
                "       b.party_name as maintenancePartyName,c.user_name as safeUserName,\n" +
                "       d.user_name as maintenanceUserFirstName,\n" +
                "       d.firstgt,\n" +
                "       case when insurance_num is not null then '是'\n" +
                "       else '否' end as isInsure\n" +
                "from elevator a\n" +
                "left join elevator_type e on e.id = a.elevator_type_id\n" +
                "left join party b on a.maintenance_party_id=b.id\n" +
                "left join sys_user c on a.safe_user_id = c.id\n" +
                "left join sys_user d on a.maintenance_user_first = d.id) S where 1=1 ");

        if(extElevator.getUsedPartyId() != null){
            builder.append(" and used_party_id = #{usedPartyId}");
        }

        if(StringUtils.isNotEmpty(extElevator.getIsInsure())){
            builder.append(" and isInsure = #{isInsure}");
        }
        if(extElevator.getMaintenancePartyId() != null){
            builder.append(" and maintenance_party_id = #{maintenancePartyId}");
        }

        //电梯id
        List<String> elevatorIds = extElevator.getElevatorIds();
        if(!CollectionUtils.isEmpty(elevatorIds)){
            StringBuilder sb1 = new StringBuilder(" and id in (");
            for(int i = 0,len = elevatorIds.size(); i < len; i++){
                if(i == len - 1){
                    sb1.append("'").append(elevatorIds.get(i)).append("')");
                }else{
                    sb1.append("'").append(elevatorIds.get(i)).append("',");
                }
            }
            builder.append(sb1);
        }

        //制造单位，可能多个值
        String createCompanyName = extElevator.getCreateCompanyName();
        if(StringUtils.isNotEmpty(createCompanyName)){
            String [] names = createCompanyName.split(",");
            StringBuilder sb = new StringBuilder(" and create_company_name in (");
            for(int i=0, len = names.length; i < len; i++){
                if(i == len - 1){
                    sb.append("'").append(names[i]).append("')");
                }else{
                    sb.append("'").append(names[i]).append("',");
                }
            }
            builder.append(sb);
        }
        if(extElevator.getElevatorTypeId() != null){
            builder.append(" and elevatorTypeId=#{elevatorTypeId}");
        }

        if(StringUtils.isNotEmpty(extElevator.getElevatorNum())){
            builder.append(" and elevatorNum=#{elevatorNum}");
        }

        //添加多种类型电梯的查询
        List<String> elevatorTypeIds = extElevator.getElevatorTypeIds();
        if(!CollectionUtils.isEmpty(elevatorTypeIds)){
            StringBuilder sb = new StringBuilder(" and elevatorTypeId in (");
            for(int i = 0,len = elevatorTypeIds.size(); i < len; i++){
                String elevatorTypeId = elevatorTypeIds.get(i);
                if(i == len - 1){
                    sb.append("'").append(elevatorTypeId).append("')");
                }else{
                    sb.append("'").append(elevatorTypeId).append("',");
                }
            }
            builder.append(sb);
        }
        builder.append(" order by id desc ");

        return builder.toString();
    }

    /**
     * 按需维保更新电梯是否参与维保与维保变更天数
     */
    public String elevatorDemandMaintanceDay(Map inMap){
        StringBuilder builder = new StringBuilder("update elevator a set ");
        StringBuilder sb = new StringBuilder("");
        if(inMap.get("isParticipateIn") != null && !"".equals(inMap.get("isParticipateIn"))){
            sb.append(" is_participate_in = #{isParticipateIn},");
        }
        if(inMap.get("maintenanceDay") != null && !"".equals(inMap.get("maintenanceDay"))){
            sb.append(" maintenance_day = #{maintenanceDay},");
            sb.append(" next_halfmon_maint_date = date_add(last_maintenance_time,INTERVAL ").append(inMap.get("maintenanceDay")).append(" day) + 0,");
        }
        builder.append(sb.substring(0,sb.lastIndexOf(","))).append(" where 1=1 ");
        if(inMap.get("elevatorIds") != null){
            builder.append(" and id in (");
            List<String> elevatorIds = (List<String>) inMap.get("elevatorIds");
            for(int i = 0,len = elevatorIds.size(); i < len; i++){
                if(i == len - 1){
                    builder.append("'").append(elevatorIds.get(i)).append("'");
                }else{
                    builder.append("'").append(elevatorIds.get(i)).append("',");
                }

            }
            builder.append(")");
        }
        return builder.toString();
    }

    /**
     *
     * 双随机一公开查询电梯信息
     * findList
     * @param
     * @return
     */
    public String getElevatorList(Map map){
        StringBuffer sb = new StringBuffer();

        sb.append(" select * from ( ");
        sb.append(" select ");
        sb.append("  r.elevator_num as 'elevatorNum', ");
        sb.append("  r.build_addr as 'buildAddr', ");
        sb.append("  r.elevator_type_id as 'elevatorTypeId', ");
        sb.append("  r.elevator_place as 'elevatorPlace' ,");
        sb.append("  r.used_party_id as 'usedPartyId',");
        sb.append("  r.maintenance_party_id as 'maintenancePartyId', ");
        sb.append("  pe.type_name as 'typeName', ");
        sb.append("  ty.party_name as 'maintenancePartyName', ");
        sb.append("  pa.party_name as 'usedPartyName', ");
        sb.append("  sub.parent_id, ");
        sb.append("  (case when DATE_FORMAT(NOW(),'%Y')-SUBSTRING(elevator_num,11,4) >=0 and DATE_FORMAT(NOW(),'%Y')-SUBSTRING(elevator_num,11,4) <10 then 0 ");
        sb.append("  when DATE_FORMAT(NOW(),'%Y')-SUBSTRING(elevator_num,11,4) >=10 and DATE_FORMAT(NOW(),'%Y')-SUBSTRING(elevator_num,11,4) <=15 then 1 ");
        sb.append("  when DATE_FORMAT(NOW(),'%Y')-SUBSTRING(elevator_num,11,4) >=15 and DATE_FORMAT(NOW(),'%Y')-SUBSTRING(elevator_num,11,4) <=20 then 2 ");
        sb.append("  else 3 end) year");
        sb.append("  from elevator r ");
        sb.append(" left join elevator_type pe ON r.elevator_type_id = pe.id ");
        sb.append(" left join party pa ON r.used_party_id =pa.id ");
        sb.append(" left join party ty ON r.maintenance_party_id = ty.id ");
        sb.append(" left join sub_party_info sub on sub.id = pa.id ");
        sb.append(" ) ta ");

        sb.append(" where 1=1 ");

        if(map.get("elevatorTypeParam") !=null ){
            sb.append(" and ta.elevatorTypeId in ("+map.get("elevatorTypeParam").toString()+") ");
        }

        if( map.get("elevatorPlaceParam") !=null){
            List<String> types = (List<String>) map.get("elevatorPlaceParam");
            StringBuilder builder = new StringBuilder(" and ta.elevatorPlace in ( ");
            for (int i = 0; i < types.size(); i++) {
                String name = types.get(i);
                if (i == types.size() - 1) {
                    builder.append("'").append(name).append("'");
                } else {
                    builder.append("'").append(name).append("'").append(",");
                }
            }
            builder.append(")");
            sb.append(builder);
        }


        if(map.get("elevatorAgeParam") !=null){
            sb.append(" and ta.year in ("+map.get("elevatorAgeParam").toString()+") ");
        }

        if(map.get("parentId") != null && !"".equals((map.get("parentId")))){
            sb.append(" and parent_id = #{parentId}");
        }
        return sb.toString();
    }


}
